/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.*;
import java.util.ArrayList;
import Module.*;

/**
 *
 * @author CCD gruppe 2
 */

public class SessionController {
    /**
     * Object used to connect to the database
     */
    private Database database = new Database();
    /**
     * Method used to register a session
     * @param session
     * @return
     */
    public int registerSession(Session session) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "Insert into session values(DEFAULT,?,?,?,?,?,?,?,?)");
            statement.setString(2, session.getName());
            statement.setString(3, session.getDate());
            statement.setString(4, session.getPlace());
            statement.setString(5, session.getStartTime());
            statement.setString(6, session.getEndTime());
            statement.setString(7, session.getEvaluation());
            statement.setString(8, session.getPreparation());
            statement.setInt(1, session.getProjectId());
            statement.executeUpdate();
            statement = conn.prepareStatement("select * from session");
            ResultSet rs = statement.executeQuery();
            int id=0;
            while (rs.next()) {
                id = rs.getInt(1);
            }
            rs.close();
            conn.close();
            return id;
        } catch (Exception e) {
            System.err.println(e);
        }
        return 0;
    }
    /**
     * Method used to get the projectLeader to a session
     * @param sessionId
     * @return
     */
    public User getProjectLeaderToSession(int sessionId) {
        Connection conn = null;
        User u = null;
        try {
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userid=userssession.userId) "
                    + "where userssession.roleId=1 and userssession.sessionId=?");
            statement.setInt(1, sessionId);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                u = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                conn.close();
                rs.close();
                return u;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return u;
    }
    
    /**
     * Method used to get the secreatries to a session
     * @param sessionId
     * @return
     */
    public User getSecretariesToSession(int sessionId) {
        Connection conn = null;
        User u = null;

        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userId=userssession.userId) "
                    + "where userssession.roleId=3 and userssession.sessionId=?");
            statement.setInt(1, sessionId);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                u = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6));
                conn.close();
                return u;
            }
            //This is returned to avoid nullpointer if thers no secretarie to a sessions
            return new User(0, "", "", "", "", "");

        } catch (Exception e) {
            System.err.println(e);
        }
        return u;
    }
    /**
     * Method used to get the experts to a sessions
     * @param sessionId
     * @return
     */
    public ArrayList<User> getExpertsToSession(int sessionId) {
        Connection conn = null;
        ArrayList<User> u = new ArrayList<User>();

        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from users left join usersSession on(users.userId=userssession.userId) "
                    + "where userssession.roleId=4 and userssession.sessionId=?");
            statement.setInt(1, sessionId);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                u.add(new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)));
            }
            conn.close();
            return u;
        } catch (Exception e) {
            System.err.println(e);
        }
        return u;
    }
    /**
     * Method userd to edit a sessions
     * @param session
     * @return
     */
    public boolean editSession(Session session) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "update session set sessionName=?, dato=?, place=?, projectId=?, "
                    + "startTime=?, endTime=?, evaluation=?, preparation=? where sessionId=?");
            statement.setString(1, session.getName());
            statement.setString(2, session.getDate());
            statement.setString(3, session.getPlace());
            statement.setInt(4, session.getProjectId());
            statement.setString(5, session.getStartTime());
            statement.setString(6, session.getEndTime());
            statement.setString(7, session.getEvaluation());
            statement.setString(8, session.getPreparation());
            statement.setInt(9, session.getId());
            statement.executeUpdate();
            conn.close();
            return true;

        } catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }
    /**
     * Method used to delete a sessions
     * @param session
     * @return
     */
    public boolean deleteSession(Session session) {
        Connection conn = null;
        //Controller controller = new Controller();

        try {
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement2 = conn.prepareStatement("delete from teamSession where sessionId=?");
            PreparedStatement statement = conn.prepareStatement("delete from session where sessionId =?");
            PreparedStatement statement3 = conn.prepareStatement("delete from usersSession where sessionId=?");
            statement.setInt(1, session.getId());
            statement2.setInt(1, session.getId());
            statement3.setInt(1, session.getId());
            statement2.executeUpdate();
            statement3.executeUpdate();
            statement.executeUpdate();
            conn.close();
            return true;
        } catch (Exception e) {
            System.err.println(e);
        }


        return false;
    }
    /**
     * Method used to delete users from a session
     * @param sessionId
     * @return
     */
    public boolean deleteUsersFromSession(int sessionId) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from usersSession where sessionId=?");
            statement.setInt(1, sessionId);
            statement.executeUpdate();
            conn.close();
            return true;
        } catch (Exception e) {
            System.err.println(e);
        }
        return false;

    }
    /**
     * Method used to find all sessions to a project
     * @param p
     * @return
     */
    public ArrayList<Session> getSessionsToProject(Project p) {
        Connection conn = null;
        ArrayList<Session> sessions = new ArrayList<Session>();
        try {
            conn = DriverManager.getConnection(database.getURL());

            PreparedStatement statement = conn.prepareStatement(
                    "select * from session where projectId =? ");
            statement.setInt(1, p.getId());
            ResultSet r = statement.executeQuery();
            while (r.next()) {
                Session s = new Session(r.getInt(1), r.getInt(2), r.getString(3), r.getString(4),
                        r.getString(5), r.getString(6), r.getString(7), r.getString(8), r.getString(9));
                sessions.add(s);
            }

            conn.close();
            return sessions;
        } catch (Exception e) {
            System.err.println(e);
        }
        return sessions;
    }
    /**
     * Method used to set users to a sessions with role and team
     * @param sessionId
     * @param users
     * @param roleId
     * @param teamId
     */
    public void setUsersToSession(int sessionId, ArrayList<User> users, int roleId, int teamId) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(
                    "insert into usersSession values(?,?,?,?)");
            statement.setInt(2, sessionId);
            statement.setInt(3, roleId);
            statement.setInt(4, teamId);
            for (int i = 0; i < users.size(); i++) {
                statement.setInt(1, users.get(i).getId());
                statement.executeUpdate();
            }
            conn.close();
        } catch (Exception e) {
            System.err.println(e);
        }
    }
    /**
     * Method used to get all the sessions to a user
     * @param userId
     * @return
     */
    public ArrayList<Session> getAlleSessionsToUser(int userId) {
        Connection conn = null;
        ArrayList<Session> sessions = new ArrayList<Session>();
        try {
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select session.* from session "
                    + "left join usersSession on(session.sessionId = usersSession.sessionId) "
                    + "where usersSession.userId=? order by dato asc");
            statement.setInt(1, userId);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                sessions.add(new Session(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5)
                        ,rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9)));
            }
            rs.close();
            conn.close();
            return sessions;

        } catch (Exception e) {
            System.err.println(e);
        }
        return sessions;
    }
}
